from flask import Flask
import pymysql
import json
import re
import sys
import requests
from pandas import DataFrame
import datetime
import threading
from loguru import logger
from common import *
app = Flask(__name__)

@app.route('/mission')
def main():
        gLock = threading.Lock()
        with Db('data_collection_server') as conn:
                cursor = conn.cursor()
                try:
                        #按需求提取字段
                        sql = r"""
                                select 
                                    a.source_id,
                                    a.data_type,
                                    a.url,
                                    a.list_method,
                                    a.header,
                                    a.spider_type,
                                    a.spider_rule,
                                    a.list_params,
                                    a.difference,
                                    b.content
                                    from
                                    (select 
                                        source_id,
                                        data_type,
                                        url,
                                        list_method,
                                        header,
                                        spider_type,
                                        spider_rule,
                                        list_params,
                                        difference 
                                        from data_source_wait
                                        limit 1
                                    )a
                                    left join parse_template b 
                                    on a.source_id = b.data_source_id
                        """
                        gLock.acquire()
                        conn.ping(reconnect=True)
                        cursor.execute(sql)
                        data = cursor.fetchall()
                        if len(data) == 0:
                                logger.info('no data')
                                return 'no data'
                        id = data[0]['source_id']
                        conn.ping(reconnect=True)
                        cursor.execute('delete from data_source_wait where source_id = % s' % id)
                        conn.commit()
                        gLock.release()
                        data_type = data[0]['data_type']
                        #有的content多个相同，以列表返回
                        temp = []
                        if len(data) > 1:
                                for i in range(len(data)):
                                        temp.append(data[i]['content'])
                                data = data[0]
                                data['content'] = temp
                                #从list_params提取type
                        else:
                                data = data[0]
                        if  data['list_params'] != '' and data['list_params'] :
                                data['type'] = json.loads(data['list_params'])['type']

                        sql = r"""
                                select * from id_to_name_handle where data_source_id = %s 
                                """ % str(data['source_id'])
                        conn.ping(reconnect=True)
                        cursor.execute(sql)
                        name = cursor.fetchone()
                        city_code = ''
                        if name is not None:
                                name = name['table_name']
                        else:
                                post_data = {'id': str(data['source_id'])}
                                url = f'http://119.3.91.12:8086/getbid/{post_data["id"]}'
                                name = json.loads(requests.get(url).text)
                                if name['message'] == '数据不存在':
                                        logger.info('数据不存在',str(data['source_id']))
                                        return json.dumps('数据不存在',ensure_ascii=False)
                                else:
                                        city_code = name['data']['cityCode']
                                        data_extra = name['data']['dataExtra']
                                        if data_extra in ('是',1):
                                                data_extra = 1
                                        else:
                                                data_extra = 0
                                        name = name['data']['dataName']
                                        sql = r"""
                                                insert into id_to_name_handle(data_source_id,table_name,city_code,data_extra,data_type) values({0},'{1}','{2}',{3},{4})
                                        """.format(data['source_id'],name,city_code,data_extra,data_type)
                                        conn.ping(reconnect=True)
                                        cursor.execute(sql)
                                        conn.commit()
                        name = name.lower()
                        data['table_name'] = name
                        #获取全部table名以判断最终时间
                        conn.ping(reconnect=True)
                        sql = r'''
                                SELECT count(*) num
                                from
                                id_to_name_handle a
                                INNER JOIN
                                information_schema.tables b
                                on a.table_name = b.table_name 
                                where a.table_name = '%s'
                        ''' % name
                        cursor.execute(sql)
                        num = cursor.fetchone()['num']
                        if num != 0:
                                conn.ping(reconnect=True)
                                cursor.execute('select max(title_date) from %s ' % name)
                                stopDate = cursor.fetchone()
                                if stopDate:
                                        stopDate = stopDate['max(title_date)']
                                        data['stopData'] = str(stopDate)
                                else:
                                        stopDate = ""
                                        data['stopData'] = ""
                        else:
                                stopDate = ""
                                data['stopData'] = ""
                        print(data['stopData'])
                        logger.info(id)
                        ls = [[id,datetime.datetime.now(),stopDate]]
                        a = DataFrame(ls)
                        a.to_csv('send_id.csv',header=None,index=None,mode='a')
                        if data['difference'] == '':
                                data['stopDate'] = ''
                        return json.dumps(data,ensure_ascii=False)
                except Exception as e:
                        logger.info(str(e))
                        ls = [[datetime.datetime.now(),e]]
                        wrong_dataframe = DataFrame(ls)
                        wrong_dataframe.to_csv('wrong_db_data.csv',mode = 'a',header = None,index = None)
                        return 'there is a wrong '


if __name__ == "__main__":
        app.run(host = '0.0.0.0',port = "5001")